import datetime
import threading

import pymysql

from common.base_view import response_json
from common.const import CODE_OK, CODE_LOGIN_FAIL, CODE_ROLE_ERROR, CODE_DELETE_FAIL, CODE_UPDATE_FAIL, \
    CODE_INSERT_FAIL, CODE_SYSTEM_ERROR
from common.jwt_utils import JWT
from config.env import MYSQLDB

_mysql_pools = {}


def get_mysql_pool(alias):
    if alias not in _mysql_pools:
        params = MYSQLDB[alias]
        params["cursorclass"] = pymysql.cursors.DictCursor
        params.update({"autocommit": 1})
        # autocommit = 1 自动提交
        _mysql_pools[alias] = pymysql.connect(**params)
    return _mysql_pools[alias]


"""
pymysql.err.InternalError: Packet sequence number wrong - got 45 expected 0
原因：

使用了多线程，多线程共享了同一个数据库连接，但每个execute前没有加上互斥锁

方法：

方法一：每个execute前加上互斥锁

        lock.acquire()
        cursor.execute(command,data)     
        lock.release()

方法二：

　　每个线程拥有自己的数据库连接，即在线程调用函数中加上数据库连接代码

方法三：

       所有线程共用一个连接池，需要考虑线程总数和连接池连接数上限的问题     
"""
lock = threading.Lock()


class MysqlClient:
    def __init__(self, conn):
        self.conn = conn
        self.cursor = self.conn.cursor()

    def exec_sql(self, sql, fetch_rows=True):
        try:
            lock.acquire()
            self.cursor.execute(sql)
            lock.release()
            # 开启手动事物提交
            # self.conn.commit()
            if fetch_rows:
                rows = self.cursor.fetchall()
                return rows
        except:
            lock.release()
            return []

    def find_one(self, sql):
        try:
            lock.acquire()
            self.cursor.execute(sql)
            lock.release()
            # self.conn.commit()
            return self.cursor.fetchone()
        except:
            lock.release()
            return {}

    def execute(self, sql):
        try:
            lock.acquire()
            self.cursor.execute(sql)
            lock.release()
            # self.conn.commit()
            return self.cursor.rowcount
        except:
            lock.release()
            return 0


class News(MysqlClient):
    def __init__(self):
        super().__init__(
            get_mysql_pool("news")
        )

    def list_func_with_all(self, table, page, where_sql, res_name, size=15):
        info_sql = 'select * from {} {}  order by updated desc limit {} OFFSET {}'
        total_sql = 'select count(*) as total from {} {}'
        if page <= 0:
            page = 1
        where = "where " + " and ".join(where_sql) if len(where_sql) else ""
        info_sql = info_sql.format(table, where, size, (page - 1) * size)
        total_sql = total_sql.format(table, where)
        print(info_sql, total_sql)
        users = self.exec_sql(info_sql)
        total = self.find_one(total_sql)
        return response_json(CODE_OK, {"total": total.get("total"), res_name: users})

    def delete_func(self, table, ids):
        sql = "delete from {} where id in ({})".format(table, ",".join(ids))
        res = self.execute(sql)
        if res:
            return response_json(CODE_OK, res > 0)
        return response_json(CODE_DELETE_FAIL, False)

    def update_by_id_func(self, table, ids, up_dict: dict):
        sql = "update {}".format(table)
        up_list = list()
        for field, value in up_dict.items():
            if value:
                v_temp = str(value).replace("'", "\\'", -1).replace('"', '\\"', -1)
                v = "'{}'".format(v_temp)
                if type(value) in (int, float):
                    v = value
                up_list.append("{} = {}".format(field, v))
        up_list.append("updated = '{}'".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
        sql += " set " + " , ".join(up_list) + " where id in ({})".format(','.join(ids))
        print(sql)
        res = self.execute(sql)
        if res:
            return response_json(CODE_OK, res > 0)
        return response_json(CODE_UPDATE_FAIL, False)

    def insert_func(self, table, insert_dict: dict, is_user=False):
        photo = insert_dict.get("photo")
        if insert_dict.get("photo") and not is_user:
            del insert_dict["photo"]
        keys = list(insert_dict.keys())
        values = list(insert_dict.values())
        now = "'{}'".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
        keys.extend(["updated", "created"])

        v = list()
        for value in values:
            if type(value) not in (int, float):
                value = value.replace("'", "\\'", -1).replace('"', '\\"', -1)
            i = "'{}'".format(value)
            if type(value) in (int, float):
                i = str(value)
            v.append(i)
        v.extend([now, now])
        sql = "INSERT INTO {} ({})  VALUES ({})".format(table, ",".join(keys), ",".join(v))
        print(sql)
        res = self.execute(sql)
        if res:
            insert_dict.update({"updated": now, "created": now, "photo": photo})
            return response_json(CODE_OK, insert_dict)
        return response_json(CODE_INSERT_FAIL, False)

    def find_one_by_id_func(self, table, id):
        sql = "select * from {} where id = {}".format(table, id)
        res = self.find_one(sql)
        if res:
            return response_json(CODE_OK, res)
        return response_json(CODE_SYSTEM_ERROR)
